package cn.lanqiao.dao;

import cn.lanqiao.entity.User1;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

//封装业务 CRUD
public class User1DaoImpl {
    String driver = "com.mysql.cj.jdbc.Driver";
    String url = "jdbc:mysql://localhost:3306/test";
    String user = "root";
    String password = "123";

    public int insert(User1 user1) throws SQLException {
        Connection conn = null;
        Statement statement = null;
        int ret = -1;
        try {
            //1. 注册驱动 (仅仅做一次)
            Class.forName(driver);
            //2. 建立连接(Connection)
            conn = DriverManager.getConnection(url, user, password);
            conn.setAutoCommit(false);
            //3. 创建运行SQL的语句(Statement)
            statement = conn.createStatement();
            //4. 运行语句
            ret = statement.executeUpdate("insert into user1 (user_name, password) values ('" + user1.getUserName() + "'," +user1.getPassword() + ")");
            //5. 处理运行结果(ResultSet) DML不需要此步骤
            conn.commit();
        } catch (Exception e) {
            conn.rollback();
            e.printStackTrace();
        } finally {
            //6. 释放资源
            try {
                statement.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return ret;
    }

    public List<User1> query() {
        Connection conn = null;
        Statement statement = null;
        ResultSet rs = null;
        List<User1> studentList = new ArrayList<>();
        try {
            //1. 注册驱动 (仅仅做一次)
            Class.forName(driver);
            //2. 建立连接(Connection)
            conn = DriverManager.getConnection(url, user, password);
            //3. 创建运行SQL的语句(Statement)
            statement = conn.createStatement();
            //4. 运行语句
            rs = statement.executeQuery("select * from user1");
            //5. 处理运行结果(ResultSet) DML不需要此步骤
            while(rs.next()){
                User1 user1 = new User1();
                user1.setUserName(rs.getString("user_name"));
                user1.setPassword(rs.getString("password"));
                user1.setCreateTime(rs.getTimestamp("create_time"));
                //其他属性赋值略
                studentList.add(user1);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //6. 释放资源
            try {
              	rs.close();
                statement.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return studentList;
    }


    public static void main(String[] args) {
//        User1 user1 = new User1();
//        user1.setUserName("pppp");
//        user1.setPassword("000000");
        User1DaoImpl user1Dao = new User1DaoImpl();
//        user1Dao.insert(user1);
        List<User1> studentList = user1Dao.query();
        for (User1 user : studentList){
            System.out.println(user);
        }
    }
}
